package DBComm;

import java.sql.SQLException;
import java.util.ArrayList;

import messages.ChatMessage;


// DBComm is the class responsible for reading and writing information to the database. An SQL implementation of a 
// database has been used for this project.  A jdbc driver is also used and an additional library is needed to 
// establish this connection: mysql-connector-java-5.1.7-bin.jar.


public class DBComm{
	private java.sql.Connection  con = null;
	private final String url = "jdbc:mysql://";
	private final String serverName= "teambananas2.ics.uci.edu"; //Scott's Server IP: teambananas2.ics.uci.edu //Robert's IP 192.168.0.102
	private final String portNumber = "987"; //MySQL: 987 (Robert), 3306 (Dan local). SQL Server 2008: 50479
	private final String databaseName= "pIMp";
	private final String userName = "pIMpAdmin"; //root
	private final String password = "ainteasy"; //Scott's root password: LopesSucksASS //pIMpAdmin: ainteasy
	private static Stats stats; //Hold the Statistics regarding IM activity to be reported to the admin
	//private final String selectMethod = "cursor"; 



	// Constructor
	public DBComm(){
		//Instantiate the database connection.
		//I think the max number of requests we can make to the MySQL server through one of these connections is 500 (at a time).
		//Or, we can make 500 max connections at a time. I know we can re-use connections; we don't have to make a new connection every time we want to do something
		//(and then close it afterward). This is easy for a small number of requests, but once we start scaling it up, I think we'll have to make multiple DBComm
		//objects, which will make multiple connections.

		//TODO test how many connections we can make.
		try{
			con = this.getConnection();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		stats = new Stats();
	}

	private String getConnectionUrl(){
		//MS SQL Server
		//return url+serverName+":"+portNumber+";databaseName="+databaseName+";selectMethod="+selectMethod+";";

		//MySQL
		return url+serverName+":"+portNumber+"/"+databaseName+"?";
	}

	private java.sql.Connection getConnection(){
		try{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			con = java.sql.DriverManager.getConnection(getConnectionUrl(),userName,password);
			if(con!=null) System.out.println("Connection Successful!" + "\n");
		}catch(Exception e){
			e.printStackTrace();
			System.err.println("Error Trace in getConnection() : " + e.getMessage());
		}
		return con;
	}
	

	// Robert Jolly
	// Method responsible for adding a chat message into the database. Returns a value of 1 if successful.  Otherwise
	// it will need to return a number corresponding to the error message provided.  
	public int addMessage(String from, String message, String to){


		java.sql.Statement st = null;
		int retVal = 0; //SERVER ERROR
		String insertChat = "INSERT INTO chatlist (`from`, `messages`, `to`, `type`,`offline`) " +
		"VALUES ('" + from + "', '" +  message + "', '" + to + "', 0, 1);";


		int i;
		System.out.println(insertChat);

		try {
			st = con.createStatement();
			i = st.executeUpdate(insertChat);
			if (i == 1){
				retVal = 1; //SUCCESS
				System.out.println("Message Added.");

				//FOR STATS - Scott
				stats.addMessage(message.length());
			}
			else{
				retVal = 3; //Most likely to or from doesn't exist, so just say invalid login
			}
			st.close();
			st = null;
		} catch (SQLException e) {
			retVal = 0; //Should be a server error...most likely it would be con.creatStatement() that failed.
			System.err.println(e);
		}
		addWebEvent(to, 1);
		return retVal;
	}
	
	// Robert Jolly
	// Method responsible for adding a chat message into the database. Returns a value of 1 if successful.  Otherwise
	// it will need to return a number corresponding to the error message provided.  
	public int addClientMessage(String from, String message, String to){

		java.sql.Statement st = null;
		int retVal = 0;
		String insertChat = "INSERT INTO chatlist (`from`, `messages`, `to`, `type`) " +
		"VALUES ('" + from + "', '" +  message + "', '" + to + "',0);";

		int i;
		System.out.println(insertChat);

		try {
			st = con.createStatement();
			i = st.executeUpdate(insertChat);
			if (i == 1){
				retVal = 1; 
				System.out.println("Message Added.");

				//FOR STATS - Scott
				stats.addMessage(message.length());
			}

			else 
				retVal = 0;

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		addWebEvent(to, 1);
		return retVal;
	}
	
	// Robert Jolly
	// Method responsible for returning an ArrayList of Chat Message Objects.  Each message is stored as one row in the 
	// database table 'chatlog'.  
	public ArrayList<ChatMessage> getMessage(String recipient){

		ChatMessage chat = new ChatMessage();

		ArrayList<ChatMessage> messages = new ArrayList<ChatMessage>();
		java.sql.Statement st = null;
		java.sql.ResultSet rs = null;

		String getChat = "SELECT * FROM chatlist WHERE `to`='"+ recipient + "' AND type = 0;";
		String updateChat = "UPDATE chatlist set type = 1 where chatlist.to = '" + recipient + "';";

		try {
			st = con.createStatement();
			rs = st.executeQuery(getChat);

			while (rs.next()){
				System.out.println("from: " + rs.getString("from"));
				chat = new ChatMessage(rs.getString("from"), rs.getString("to"), rs.getString("messages"));	
				messages.add(chat);
			}

			st = con.createStatement();
			int i = st.executeUpdate(updateChat);
			if(i == 1){
				//update successful
			}
			rs.close();
			rs = null;
			st.close();
			st = null;
		} catch (SQLException e) {
			System.err.println(e);
		}
		return messages;
	}
	
	
	// Robert Jolly
	// Method responsible for returning an ArrayList of Chat Message Objects.  Each message is stored as one row in the 
	// database table 'chatlog'.  
	public ArrayList<ChatMessage> getClientMessage(String recipient){

		ChatMessage chat = new ChatMessage();

		ArrayList<ChatMessage> messages = new ArrayList<ChatMessage>();
		java.sql.Statement st = null;
		java.sql.ResultSet rs = null;

		String getChat = "SELECT * FROM chatlist WHERE `to`='"+ recipient + "' AND type = 0; AND offline = 0";
		String updateChat = "UPDATE chatlist set type = 1 where chatlist.to = '" + recipient + "';";

		try {
			st = con.createStatement();
			rs = st.executeQuery(getChat);

			while (rs.next()){
				System.out.println("from: " + rs.getString("from"));
				chat = new ChatMessage(rs.getString("from"), rs.getString("to"), rs.getString("messages"));	
				messages.add(chat);
			}

			st = con.createStatement();
			int i = st.executeUpdate(updateChat);
			if(i == 1){
				//update successful
			}
			rs.close();
			rs = null;
			st.close();
			st = null;
		} catch (SQLException e) {
			System.err.println(e);
		}
		return messages;
	}

	//Dan Morgan
	//This method updates the status, an integer, of the specified client.
	public int updateStatus(String clientid, int status){
		int retVal = 0; //server error
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectClient = "SELECT * FROM client where clientid = '" + clientid + "';";
		String updateStatus = "UPDATE client set status = '" + status + "' WHERE clientid = '" + clientid + "';";

		try{
			if(con!=null){            
				st = con.createStatement();
				rs = st.executeQuery(selectClient);

				//check if the clientid exists before updating the status
				if(rs.next()){
					//if they exist, we can try to update their status
					try{
						//executeUpdate() returns an integer; a return of 1 means the insert was successful.
						int i = st.executeUpdate(updateStatus);
						if(i == 1){
							retVal = 1;//updateStatus successful 
							System.out.println("updateStatus successful." + "\n");

							//FOR STATS - SCOTT
							stats.changedStatus();
						}
					}
					catch(Exception e)
					{
						System.err.println("updateStatus failed." + "\n");
						System.err.println(e + "\n");
						retVal = 0; //Server error...we found the client but the executeUpdate failed
					}
				}
				retVal = 3; //invalid login
				rs.close();
				rs = null;
				st.close();
				st = null;
			}
			else{
				retVal = 0; //Server error...con.creatStatement most likely failed.
				System.out.println("Error: No active Connection" + "\n");
			}
		}catch(Exception e){
			retVal = 0; //server error, con not created
			System.err.println(e + "\n");
		}
		this.updateBuddies(clientid);
		//return the error/success code to the caller so they can quickly parse what happened
		return retVal;
	}


	//Dan Morgan
	//This method gets the status, as an integer, of the specified client.
	//If this method returns -1, an error occured
	public int getStatus(String clientid){
		int retVal = -1; //server error
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectStatus = "SELECT status FROM client where clientid = '" + clientid + "';";

		try{
			if(con!=null){      	  
				st = con.createStatement();
				rs = st.executeQuery(selectStatus);

				//rs.next() will return true if the client exists
				if(rs.next()){
					//if it's true, we found the clientid, and can extract their status
					retVal = new Integer(rs.getString(1)).intValue();
				}
				else{
					System.err.println("That clientID does not exist. Please try another");
					retVal = 3; //invalid login
				}
				rs.close();
				rs = null;
				st.close();
				st = null;
			}else{
				retVal = 0; //server error
				System.err.println("Error: No active Connection" + "\n");
			}
		}catch(Exception e){
			System.err.println(e + "\n");
			retVal = 0; //server error
		}
		return retVal;
	}


	//Robert Jolly 
	// This will add a webevent to the database table.  

	public int addWebEvent(String clientID, int type){
		java.sql.Statement st = null;
		String insertEvent = "INSERT INTO event(ClientID, Type) "  + "VALUES('" + clientID + "', " + type + ");";
		
		String update1 = "UPDATE event SET Type=1 WHERE ClientID = '" + clientID + "';";
		String update2 = "UPDATE event SET Type=2 WHERE ClientID = '" + clientID + "';";
		String update3 = "UPDATE event SET Type=3 WHERE ClientID = '" + clientID + "';";

		int retVal = 0; //Server Error

		try{
			if(con!=null){  

				int checker = getWebEvent(clientID);
				System.out.println(checker);

				if (checker == 1 && type == 1){
					st = con.createStatement();
					st.executeUpdate(update1);
				retVal = 1; 
				}
				
				else if (checker == 2 && type == 1){
					st = con.createStatement();
					st.executeUpdate(update3);
				retVal = 1; 
				}
				
				else if (checker == 3 && type == 1){
					st = con.createStatement();
					st.executeUpdate(update3);
				retVal = 1; 
				}
				
				else if (checker == 2 && type == 3){
					st = con.createStatement();
					st.executeUpdate(update2);
				retVal = 1; 
				}
				
				else if (checker == 3 && type == 2){
					st = con.createStatement();
					st.executeUpdate(update3);
				retVal = 1; 
				}
				
				else if (checker == 1 && type == 2){
					st = con.createStatement();
					st.executeUpdate(update3);
				retVal = 1; 
				}

				else if (checker == 1 && type == 3){
					System.out.println(update3);
					st = con.createStatement();
					st.executeUpdate(update3);
				retVal = 1; 
				}
				
				else if (checker == 3 && type == 3){
					st = con.createStatement();
					st.executeUpdate(update3);
				retVal = 1; 
				}
				
				else st = con.createStatement();
					st.executeUpdate(insertEvent);
					retVal = 1; 
				
				
				
				//Currently 1 & add 1 
				//Currently 2 & add 1 make it a three
				//Currently 3 & add 1 make it a three
				//Current 2 & and add 2 stay a two
				//Currently 2 & add a three, becomes a three
				//Cureently 3 & add a two remains a three
				//Currently 1 & add a 2 it remains a three
				//Currently 1 and add a three it will become a three
				//Currently a three and want to add a three it will remain a three
				//if nothing then it will put in anything.  

			}
			st.close();
			st = null;
		}
		
		//if the event is already present, it will throw an exception
		catch(Exception e)
		{
			System.err.println("Your event is already present." + "\n");
			System.err.println(e);
			retVal = 7; //Your event is already present
		}

		//return the error/success code to the caller so they can quickly parse what happened
		return retVal;
	}
	
	public int clearEvent(String clientID){
		
		java.sql.Statement st = null;
		String deleteEvent = "DELETE FROM event WHERE ClientID='" + clientID + "';";
		int retVal = 0; //Server Error

		try{
			if(con!=null){      	  
				st = con.createStatement();
				retVal = st.executeUpdate(deleteEvent);
			}
			st.close();
			st = null;
		}
		//if the event is already present, it will throw an exception
		catch(Exception e)
		{
			System.err.println("Event not found." + "\n");
			System.err.println(e + "\n");
			retVal = 7; //Your event is already present
		}
		//return the error/success code to the caller so they can quickly parse what happened
		return retVal;
	}

	//Chad Curtis
	// This will return a webevent from the database table.  
	public int updateBuddies(String clientID){
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectEvent = "SELECT clientID FROM Buddy b WHERE b.Buddy='" + clientID + "';";
		
		System.out.println(selectEvent);
		int retVal = 0; //Server Error
		
		try{
			if(con!=null){      	  
				st = con.createStatement();
				rs = st.executeQuery(selectEvent);
				
				//iterate through the results and add them to the Events
				while(rs.next()){
					//if it's true, we found the clientid, and can extract their status
					addWebEvent(rs.getString(1), 2);
					retVal = 1;
				}
			}
			rs.close();
			rs = null;
			st.close();
			st = null;
		}
		//if the event is already present, it will throw an exception
		catch(Exception e)
		{
			System.err.println("Your event is already present." + "\n");
			System.err.println(e + "\n");
			retVal = 7; //Your event is already present
		}
		//return the error/success code to the caller so they can quickly parse what happened
		return retVal;
	}
	
	
	//Robert Jolly
	// This will return a webevent from the database table.  
	public int getWebEvent(String clientID){
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectEvent = "SELECT type FROM event WHERE ClientID='" + clientID + "';";
		int retVal = 0; //Server Error

		try{
			if(con!=null){      	  
				st = con.createStatement();
				rs = st.executeQuery(selectEvent);
				
				//rs.next() will return true if the client exists
				if(rs.next()){
					//if it's true, we found the clientid, and can extract their status
					retVal = new Integer(rs.getString(1)).intValue();
				}
			}
			rs.close();
			rs = null;
			st.close();
			st = null;
		}
		//if the event is already present, it will throw an exception
		catch(Exception e)
		{
			System.err.println("Your event is already present." + "\n");
			System.err.println(e + "\n");
			retVal = 7; //Your event is already present
		}
		//return the error/success code to the caller so they can quickly parse what happened
		return retVal;
	}
	

	//Dan Morgan
	//This method will add a buddy to the buddy table.
	//ClientID is the person adding the buddy.
	//Buddy is...the buddy.
	//Group is their group in the buddy list (all buddies, friends, etc).
	public int addBuddy(String clientID, String buddy, String group){
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectClient = "SELECT clientid FROM Client WHERE clientID = '" + clientID + "';";
		String selectBuddy = "SELECT clientid FROM Client WHERE clientID = '" + buddy + "';";
		String insertBuddy = "INSERT INTO buddy (clientid, buddy, usergroup) VALUES ('" + clientID + "','" + buddy + "','"+ group + "');";
		int retVal = 0; //Server Error
		
		try{
			if(con!=null){      	  
				st = con.createStatement();
				rs = st.executeQuery(selectClient);

				//check if the original client exists
				//rs.next() will return true if the select statement found the client in the database, meaning he exists
				if(rs.next()){
					//if he exists, now we check if the buddy we're adding exists
					rs = st.executeQuery(selectBuddy);
					if(rs.next()){
						//if both exist, we can add the buddy
						try{
							//executeUpdate() returns an integer; a return of 1 means the insert was successful.
							int i = st.executeUpdate(insertBuddy);
							if(i == 1){
								retVal = 1;//addBuddy successful 
								System.out.println("AddBuddy successful." + "\n");

								//FOR STATS - Scott
								stats.moreBuddies();
							}
						}
						//if the two are already buddies, it will throw an exception
						catch(Exception e)
						{
							//e.printStackTrace();
							System.err.println("Your users are already buddies." + "\n");
							System.err.println(e + "\n");
							retVal = 5; //Your users are already buddies
						}
					}
				}
				else {
					retVal = 3;//Invalid ClientID 
				}   			 
				rs.close();
				rs = null;
				st.close();
				st = null;
			}else System.err.println("Error: No active Connection" + "\n");
		}catch(Exception e){
			e.printStackTrace();
		}	
		//return the error/success code to the caller so they can quickly parse what happened
		return retVal;
	}

	//Dan Morgan
	//This method will remove a buddy from the buddy table.
	//You must supply the ClientID (the person removing the buddy), the Buddy (the buddy to be removed), and the buddy's group (all buddies, friends, etc).
	//The reason is all three are primary keys in the database, so you must supply all three.
	public int removeBuddy(String clientID, String buddy, String group){
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectBuddy = "SELECT ClientID, Buddy, UserGroup FROM Buddy WHERE ClientID = '" + clientID + "' AND Buddy = '" + buddy +
		"' AND UserGroup = '" + group + "';";
		String deleteBuddy = "DELETE FROM buddy WHERE clientID = '" + clientID + "' AND buddy = '" + buddy + "' AND userGroup ='"+ group + "'";
		int retVal = 0; //Server Error

		try{
			if(con!=null){      	  
				st = con.createStatement();
				rs = st.executeQuery(selectBuddy);

				//check and see if the buddy exists in the table
				//rs.next() will return true if the select statement found the buddy in the database, meaning he exists
				if(rs.next()){
					//if they exist, we can try to delete the buddy
					try{
						//executeUpdate() returns an integer; a return of 1 means the insert was successful.
						int i = st.executeUpdate(deleteBuddy);
						if(i == 1){
							retVal = 1;//removeBuddy successful 
							System.out.println("RemoveBuddy successful." + "\n");
						}
					}
					catch(Exception e)
					{
						//e.printStackTrace();
						System.err.println("Remove buddy failed." + "\n");
						System.err.println(e + "\n");
						retVal = 6; //TODO what is this error
					}              		  
				}
				else {
					retVal = 3;//Invalid buddy, cannot delete because it doesn't exist 
				}
				rs.close();
				rs = null;
				st.close();
				st = null;
			}else System.err.println("Error: No active Connection" + "\n");
		}catch(Exception e){
			//e.printStackTrace();
			System.err.println(e + "\n");
		}
		//return the error/success code to the caller so they can quickly parse what happened	
		return retVal;
	}

	//Dan Morgan
	//This method will return a buddylist object, which is basically an arraylist of buddy strings.
	public BuddyList getBuddyList(String clientid){
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectBuddys = "SELECT b.buddy, c.status FROM buddy b, client c WHERE b.clientid = '" + clientid + "' AND b.buddy = c.clientid;";
		BuddyList buddyList = new BuddyList();

		try{
			if(con!=null){      	  
				st = con.createStatement();
				rs = st.executeQuery(selectBuddys);

				//iterate through the results and add them to the BuddyList
				while (rs.next()){
					buddyList.addBuddy( new Buddy(rs.getString(1), Integer.parseInt(rs.getString(2))));
				}
				rs.close();
				rs = null;
				st.close();
				st = null;
			}else System.out.println("Error: No active Connection" + "\n");
		}catch(Exception e){
			System.err.println(e + "\n");
		}
		return buddyList;
	}

	// Robert Jolly
	public String getChatHistory(String clientID){

		String history = "";
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;

		String chatHistory = "SELECT ChatLog FROM chatlog WHERE ClientID = '" + clientID + "';";

		try{
			if (con!=null){
				st = con.createStatement();
				rs = st.executeQuery(chatHistory);

				history = rs.getString(1); 
			}
			rs.close();
			rs = null;
			st.close();
			st = null;
		} catch(Exception e){
			System.out.println("Unable to retrieve chat history.");
			System.err.println(e + "\n");
		}

		return history;
	}

	public int verifyLogin(String loginID, String password){
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectPassword = "SELECT password FROM Client WHERE clientID = '" + loginID + "' AND lockedout = 0;";
		int retVal = 0; //Server Error

		try{
			if(con!=null){      	  
				st = con.createStatement();
				rs = st.executeQuery(selectPassword);

				//rs.next() will return true if the select statement found the client in the database, meaning he exists, and if the client is not locked out.
				if(rs.next()){					
					//if he exists, we check the password
					if(password.equals(rs.getString(1))){
						retVal = 1;//Login successful 
					}
					else{
						retVal = 2;//Bad password
					}                		  
				}
				else {
					retVal = 3;//Invalid ClientID, or the client is locked out.
				}
				rs.close();
				rs = null;
				st.close();
				st = null;
			}else System.err.println("Error: No active Connection" + "\n");
		}catch(Exception e){
			System.err.println(e + "\n");
		}
		//return the error/success code to the caller so they can quickly parse what happened
		return retVal;
	}  

	//Dan Morgan
	//This method will register a new user with the client. You must supply a clientID and a password.
	//adds a new user to the client table and sets default configurations in the configuration table
	//TODO for later would be allowing updating of email, status, and lockedout.
	public int registerUser(String clientID, String password){
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectClient = "SELECT clientid FROM Client WHERE clientID = '" + clientID + "';";
		//status and lockedout will default to 0
		//email will default null
		//insert time timestamp when this method is called.
		long timestamp = System.currentTimeMillis();
		String insertClient = "INSERT INTO Client (clientid, email, password, status, lockedout, timestamp) VALUES ('" 
			+ clientID + "',null,'"+ password + "',0,0,"+ timestamp + ");";
		//the default configuration
		String insertConfiguration = "INSERT INTO configuration (clientid, tabfill, tabstroke, windowfill, windowstroke, chatwindowcolor, textcolor) VALUES" +
		" ('" + clientID + "','0x000000','0x000000','0x000000','0x000000','0x000000','0x000000');"; 

		int retVal = 0; //Server Error

		try{
			if(con!=null){            
				st = con.createStatement();
				rs = st.executeQuery(selectClient);

				//check if the clientid already exists
				//rs.next() will return true if the select statement found the client in the database, meaning he exists
				//therefore, we want !rs.next()
				if(!rs.next()){
					//if he doesn't exist, we can add him
					try{
						//executeUpdate() returns an integer; a return of 1 means the insert was successful.
						//first we'll add the new client
						int i = st.executeUpdate(insertClient);
						if(i == 1){
							System.out.println("RegisterUser successful.");
							//executeUpdate() returns an integer; a return of 1 means the insert was successful.
							//now we can set his initial configuration
							int j = st.executeUpdate(insertConfiguration);
							if(j == 1){
								retVal = 1;//registerUser and setInitialConfig successful 
								System.out.println("SetConfiguration successful.");
								System.out.println("New user and configuration successfully added." + "\n");

								//FOR STATS - Scott
								stats.adduser();
							}
						}
					}
					catch(Exception e)
					{
						System.err.println(e + "\n");
						retVal = 0; //server error
					}
				}
				else {
					System.err.println("That ClientID already exists." + "\n");
					retVal = 3;//Invalid ClientID 
				}                        
				rs.close();
				rs = null;
				st.close();
				st = null;
			}else System.err.println("Error: No active Connection" + "\n");
		}catch(Exception e){
			System.err.println(e);
		}
		//return the error/success code to the caller so they can quickly parse what happened
		return retVal;
	}

	//Dan Morgan
	//This method will lock out a user from logging in to the chat system.
	//This should be called after 5 failed login attempts. The user will be unable to log in until unLockOutUser is called on their name.
	public int lockOutUser(String clientID){
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectClient = "SELECT clientid FROM Client WHERE clientID = '" + clientID + "';";
		String updateLockedOut = "UPDATE client set lockedout = 1 where clientid = '" + clientID + "';"; 
		int retVal = 0; //Server Error

		try{
			if(con!=null){      	  
				st = con.createStatement();
				rs = st.executeQuery(selectClient);

				//check if the original client exists
				//rs.next() will return true if the select statement found the client in the database, meaning he exists
				if(rs.next()){
					//if he exists, we can update his lockedout status
					try{
						//executeUpdate() returns an integer; a return of 1 means the insert was successful.
						int i = st.executeUpdate(updateLockedOut);
						if(i == 1){
							retVal = 1;//updateLockedOut successful 
							System.out.println("lockOutUser successful." + "\n");
						}
					}
					//MySQL doesn't seem to throw an error even if you give it an invalid clientid
					//It just says "Query OK, 0 rows affected" so I'm not expecting this to throw an error.
					catch(Exception e)
					{
						//e.printStackTrace();
						System.err.println(e + "\n");
						retVal = 0; //server error
					}
				}
				else {
					retVal = 3;//Invalid ClientID 
				}			 
				rs.close();
				rs = null;
				st.close();
				st = null;
			}else System.err.println("Error: No active Connection" + "\n");
		}catch(Exception e){
			e.printStackTrace();
		}	
		//return the error/success code to the caller so they can quickly parse what happened
		return retVal;
	}

	//Dan Morgan
	//Removes the lock from the client's username so they can log in again.
	public int unLockOutUser(String clientID){
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectClient = "SELECT clientid FROM Client WHERE clientID = '" + clientID + "';";
		String updateLockedOut = "UPDATE client set lockedout = 0 where clientid = '" + clientID + "';"; 
		int retVal = 0; //Server Error

		try{
			if(con!=null){      	  
				st = con.createStatement();
				rs = st.executeQuery(selectClient);

				//check if the original client exists
				//rs.next() will return true if the select statement found the client in the database, meaning he exists
				if(rs.next()){
					//if he exists, we can update his lockedout status
					try{
						//executeUpdate() returns an integer; a return of 1 means the insert was successful.
						int i = st.executeUpdate(updateLockedOut);
						if(i == 1){
							retVal = 1;//updateLockedOut successful 
							System.out.println("unLockOutUser successful." + "\n");
						}
					}
					//MySQL doesn't seem to throw an error even if you give it an invalid clientid
					//It just says "Query OK, 0 rows affected" so I'm not expecting this to throw an error.
					catch(Exception e)
					{
						//e.printStackTrace();
						System.err.println(e + "\n");
						retVal = 0; //server error
					}
				}
				else {
					retVal = 3;//Invalid ClientID 
				}			 
				rs.close();
				rs = null;
				st.close();
				st = null;
			}else System.err.println("Error: No active Connection" + "\n");
		}catch(Exception e){
			System.err.println(e);
		}	
		//return the error/success code to the caller so they can quickly parse what happened
		return retVal;
	}

	//Dan Morgan
	//This method sets a client's configuration, or preferences, which you can see listed below (the index references). These are for colors in the chat client.
	//You must supply a Configuration object, which is basically an arraylist of strings of the below preferences.
	public int updateConfiguration(String clientID, Configuration _config){
		//index 0 = _clientID;
		//index 1 = _tabFill;
		//index 2 =_tabStroke;
		//index 3 = _windowFill;
		//index 4 = _windowStroke;
		//index 5 = _chatWindowColor;
		//index 6 = _textColor;
		ArrayList<String> configList = _config.getConfiguration();
		int retVal = 0; //Server error
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectClientID = "SELECT clientid FROM client where clientid = '" + clientID + "';";
		String updateConfiguration = "UPDATE configuration set tabfill = '" + configList.get(1) + "', tabstroke = '" + configList.get(2) +
		"', windowfill = '" + configList.get(3) + "', windowstroke = '" + configList.get(4) + "', chatwindowcolor = '" + configList.get(5) +
		"', textcolor = '" + configList.get(6) + "' WHERE clientid = '" + clientID + "';";

		try{
			if(con!=null){            
				st = con.createStatement();
				rs = st.executeQuery(selectClientID);

				//check if the clientid exists before inputing into configuration table
				if(rs.next()){
					//if they exist, we can try to set their configuration
					try{
						//executeUpdate() returns an integer; a return of 1 means the insert was successful.
						int i = st.executeUpdate(updateConfiguration);
						if(i == 1){
							retVal = 1;//updateConfiguration successful 
							System.out.println("updateConfiguration successful." + "\n");
						}
					}
					catch(Exception e)
					{
						//e.printStackTrace();
						System.err.println("updateConfiguration failed." + "\n");
						System.err.println(e + "\n");
						retVal = 0; //server error
					}
				}
				rs.close();
				rs = null;
				st.close();
				st = null;
			}else System.out.println("Error: No active Connection" + "\n");
		}catch(Exception e){
			System.err.println(e + "\n");
		}
		//return the error/success code to the caller so they can quickly parse what happened
		return retVal;
	}

	//Dan Morgan
	//this will return a configuration object full of nulls if the clientID doesn't exist...maybe we should fix this.
	//This is because making a new configuration creates an arraylist with 7 nulls inserted, since specifying a size of the list
	//wasn't enough to say _configuration.set(index, string)...it threw index out of bounds errors.
	public Configuration getConfiguration(String clientID){
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		Configuration _configuration = new Configuration();
		String selectConfiguration = "SELECT * from configuration WHERE clientid = '" + clientID + "';"; 

		try{
			if(con!=null){      	  
				st = con.createStatement();
				rs = st.executeQuery(selectConfiguration);

				//this will return true if the clientid exists in the table
				if(rs.next()){
					//if they exist, we can iterate their configuration
					//iterate through the results and add them to a configuration object
					System.out.println("Creating the config object...");
					_configuration.updateConfiguration(0, clientID);
					for(int i = 2; i < 8; i++){
						_configuration.updateConfiguration(i-1, rs.getString(i));
					}
					System.out.println("Creation completed." + "\n");
				}
				else if(!rs.next()){
					System.err.println("That ClientID does not exist.");
				}
				rs.close();
				rs = null;
				st.close();
				st = null;
			}else System.out.println("Error: No active Connection" + "\n");
		}catch(Exception e){
			System.err.println(e + "\n");
		}
		return _configuration;
	}


	//Outputs the stats regaurding the IM activity from the stats obj. -Scott
	public static void outStats()
	{
		stats.printStats();
	}


	//Dan Morgan
	//This method doesn't serve any practical purposes, but is nice for debugging.
	public void selectAllFrom(String table){
		java.sql.ResultSetMetaData rsmd = null;
		java.sql.ResultSet rs = null;
		java.sql.Statement st = null;
		String selectAllFromClient = "SELECT * FROM " + table;

		try{
			if(con!=null){      	  
				st = con.createStatement();
				rs = st.executeQuery(selectAllFromClient);
				rsmd = rs.getMetaData();

				System.out.println("Table: " + rsmd.getTableName(1));
				System.out.println("********************************");
				//iterate through the results and print them
				while (rs.next()){
					for (int i = 1; i <= rsmd.getColumnCount(); i++){
						System.out.println("	" + rsmd.getColumnName(i)+ ": " + rs.getString(i));
					}
					System.out.println("");
				}
				System.out.println("********************************" + "\n");
				rs.close();
				rs = null;
				rsmd = null;
				st.close();
				st = null;
			}else System.out.println("Error: No active Connection" + "\n");
		}catch(Exception e){
			//e.printStackTrace();
			System.err.println(e + "\n");
		}
	}  



	public void closeConnection(){
		try{
			this.outStats();
			if(con!=null)
				con.close();
			System.out.println("The connection has been closed." + "\n");
			con=null;
		}catch(Exception e){
			//e.printStackTrace();
			System.err.println(e + "\n");
		}
	}

	public static void main(String[] args) throws Exception
	{
		System.out.println("Database connection initializing....");
		DBComm myDBComm = new DBComm();

		//myDBComm.selectAllFrom("client");
		myDBComm.addBuddy("number1pimp", "realnumber1pimp", "Buddies");
		myDBComm.addBuddy("realnumber1pimp", "number1pimp", "Homies");
		//myDBComm.selectAllFrom("buddy");
		//Testing correct ClientID and Password
		int i = myDBComm.verifyLogin("number1pimp", "bigpimpin");
		System.out.println("Number should be 1: "+ i);
		//Testing incorrect Password
		int j = myDBComm.verifyLogin("number1pimp", "asdlgkjaslg");
		System.out.println("Number should be 2: "+ j);
		//Testing incorrect ClientID
		int k = myDBComm.verifyLogin("number2pimp", "bigpimpin");
		System.out.println("Number should be 3: "+ k + "\n");
		myDBComm.removeBuddy("number1pimp", "realnumber1pimp", "Buddies");
		//myDBComm.selectAllFrom("buddy");
		myDBComm.registerUser("Dan2", "test2");
		//myDBComm.selectAllFrom("client");

		myDBComm.addMessage("Robert", "Hey Jordan", "Jordan");
		myDBComm.getMessage("Robert");

		myDBComm.addWebEvent("Dan1", 1);

		BuddyList newBuddyList = myDBComm.getBuddyList("jordan");
		System.out.println("jordan's BuddyList:" + "\n");

		for(Buddy b: newBuddyList.getBuddyList()){
			System.out.println(b.toString());
		}
		System.out.println("");

		Configuration commConfigTestInput = new Configuration();
		commConfigTestInput.updateConfiguration(0, "Dan1");
		commConfigTestInput.updateConfiguration(1, "tabFillTest1");
		commConfigTestInput.updateConfiguration(2, "tabStrokeTest1");
		commConfigTestInput.updateConfiguration(3, "windowFillTest1");
		commConfigTestInput.updateConfiguration(4, "windowStrokeTest1");
		commConfigTestInput.updateConfiguration(5, "chatWindowColorTest1");
		commConfigTestInput.updateConfiguration(6, "textColorTest1");
		myDBComm.updateConfiguration("Dan1", commConfigTestInput);

		Configuration commConfigTestOutput = myDBComm.getConfiguration("Dan1");
		System.out.println("Configuration Testing");
		System.out.println("**********************************");
		for(String s: commConfigTestOutput.getConfiguration()){
			System.out.println(s);
		}
		System.out.println("**********************************");
		System.out.println("");


		System.out.println("getStatus Testing");
		System.out.println("**********************************");
		System.out.println("Dan1: " + myDBComm.getStatus("Dan1"));
		System.out.println("ditch: " + myDBComm.getStatus("ditch"));
		System.out.println("Josh: " + myDBComm.getStatus("Josh"));
		System.out.println("Jordan1: " + myDBComm.getStatus("Jordan1"));
		System.out.println("**********************************");
		System.out.println("");

		System.out.println("updateStatus Testing");
		System.out.println("**********************************");
		myDBComm.updateStatus("Dan1", 1);
		myDBComm.updateStatus("ditch", 2);
		myDBComm.updateStatus("Josh", 3);
		myDBComm.updateStatus("Jordan1", 4);
		System.out.println("**********************************");
		//myDBComm.selectAllFrom("client");
		System.out.println("");

		System.out.println("lockOutUser Testing");
		System.out.println("**********************************");
		myDBComm.lockOutUser("Dan4");
		//Testing locked out
		int l = myDBComm.verifyLogin("Dan4", "test4");
		System.out.println("Number should be 3: "+ l + "\n");
		System.out.println("**********************************");
		System.out.println("");

		System.out.println("unLockOutUser Testing");
		System.out.println("**********************************");
		myDBComm.unLockOutUser("Dan4");
		//Testing un locked out
		int m = myDBComm.verifyLogin("Dan4", "test4");
		System.out.println("Number should be 1: "+ m + "\n");
		System.out.println("**********************************");
		System.out.println("");

		myDBComm.getWebEvent("");
		System.out.println(myDBComm.getWebEvent("George Washington"));

		myDBComm.addWebEvent("pyrobug", 3);
		System.out.println(myDBComm.addWebEvent("George Washington", 2));

		myDBComm.getMessage("Jordan");
		System.out.println(myDBComm.getMessage("Jordan"));
		
		myDBComm.closeConnection();
		myDBComm = null;

		//STATS
		outStats();
	}
}

